package com.zhledu.exam;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class DataUtil {
    public static ArrayList<Room> roomList = new ArrayList<>();
    public static ArrayList<Student> studentList = new ArrayList<>();

    public static final String sw = "2023年3月21日 09:00-11:30";
    public static final String xw = "2023年3月21日 14:00-16:30";
    public static final String cz_mc = "财经商贸类职业技能操作测试";
    public static final String cz_dd = "宁夏财经职业技术学院1号实训楼";

    public static void initRoom() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.1.8:3306/财经分类考试", "root", "zhli2008");
            String sql = "select * from t_room";
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                roomList.add(new Room(rs.getString("room_num"), rs.getString("room_name"), rs.getInt("num")));
            }
            System.out.println("考场初始化完毕。。。。。。");
            rs.close();
            stmt.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void initStudent() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.1.8:3306/财经分类考试", "root", "zhli2008");
            //String sql = "select sfzh from zkkzk647408 union select sfzh from zkkbk647408";
            String sql = "SELECT t2.SFZH from zkkbk647402 t2 UNION SELECT t3.sfzh from zkkbk647403 t3 UNION SELECT t5.sfzh from zkkzk647405 t5";
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Student student = new Student();
                student.setSfzh(rs.getString("sfzh"));
                studentList.add(student);
            }
            System.out.println("学生初始化完毕。。。。。。");
            rs.close();
            stmt.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void start() throws Exception {
        int done = 0;
        for (int n = 0; n < 2; n++) {
            for (int i = 0; i < roomList.size(); i++) {
                for (int j = 0; j < roomList.get(i).getNum(); j++) {
                    if (j + done < studentList.size()) {
                        String kch = String.format("%02d", i + 1);
                        String zwh = String.format("%03d", j + 1);
                        int index = done + j;
                        Student student = studentList.get(done + j);
                        student.setZkzh("6474" +(n+1)+ kch + zwh);
                        student.setCzmc(cz_mc);
                        student.setCzdd(cz_dd);
                        student.setCzsj(n == 0 ? sw : xw);
                        student.setCzkch(kch);
                        student.setCzzwh(zwh);
                        student.setFlag("1");
                       // System.out.println(student);
                    }
                }
                done += roomList.get(i).getNum();
            }
        }

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.1.8:3306/财经分类考试?useUnicode=true&characterEncoding=GB2312", "root", "zhli2008");
        for (int i = 0; i < studentList.size(); i++) {
            Student student = studentList.get(i);
            String sql1 = "update zkkbk647402 set cz_mc='" + student.getCzmc() + "',cz_dd='" + student.getCzdd() + "',cz_sj='" + student.getCzsj() + "',zkzh='" + student.getZkzh() + "',cz_kch='" + student.getCzkch() + "',cz_zwh='" + student.getCzzwh() + "'  where sfzh='" + student.getSfzh() + "'";
            String sql2 = "update zkkbk647403 set cz_mc='" + student.getCzmc() + "',cz_dd='" + student.getCzdd() + "',cz_sj='" + student.getCzsj() + "',zkzh='" + student.getZkzh() + "',cz_kch='" + student.getCzkch() + "',cz_zwh='" + student.getCzzwh() + "'  where sfzh='" + student.getSfzh() + "'";
            String sql3 = "update zkkzk647405 set cz_mc='" + student.getCzmc() + "',cz_dd='" + student.getCzdd() + "',cz_sj='" + student.getCzsj() + "',zkzh='" + student.getZkzh() + "',cz_kch='" + student.getCzkch() + "',cz_zwh='" + student.getCzzwh() + "'  where sfzh='" + student.getSfzh() + "'";
            Statement stmt = connection.createStatement();
            stmt.executeUpdate(sql1);
            stmt.executeUpdate(sql2);
            stmt.executeUpdate(sql3);
            System.out.println(i);
        }
        connection.close();
    }


    public static void main(String[] args) throws Exception {
        initRoom();
        initStudent();
        start();
        System.out.println("执行完毕");
    }

}
